﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.IO;

namespace PatientInformationExtract.MainInterface.Models.XMLCS
{
    class FullExcelOperate
    {
        private Microsoft.Office.Interop.Excel.Application excelApp;
        public int rowsint { set; get; }
        public int columnsint { set; get; }
        public string sheetName { set; get; }
        public string fileName { set; get; }
        public int hdr { set; get; }
        private Workbook excelWB;//创建工作簿（WorkBook：即Excel文件主体本身）  
        private Workbooks excelWBs;
        private Worksheet excelWS;//创建工作表（即Excel里的子表sheet）
        private Sheets excelSts;

        public FullExcelOperate(string filePath, int hdr)
        {
            this.hdr = hdr;
            FileInfo f = new FileInfo(filePath);
            fileName = f.Name;
            excelApp = new Microsoft.Office.Interop.Excel.Application();
            if (excelApp == null)
            {
                throw new Exception("打开Excel应用时发生错误！");
            }
            excelWBs = excelApp.Workbooks;
            //打开一个现有的工作薄  
            excelWB = excelWBs.Add(filePath);
            excelSts = excelWB.Sheets;
            //选择第一个Sheet页  
            excelWS = excelSts.get_Item(1);

            sheetName = excelWS.Name;
            rowsint = excelWS.UsedRange.Cells.Rows.Count; //得到行数
            columnsint = excelWS.UsedRange.Cells.Columns.Count;//得到列数
        }

        public void Release()
        {
            ReleaseCOM(excelWS);
            ReleaseCOM(excelSts);
            ReleaseCOM(excelWB);
            ReleaseCOM(excelWBs);
            excelApp.Quit();
            ReleaseCOM(excelApp);
        }

        public System.Data.DataTable ToDataTable()
        {
            System.Data.DataTable dt = new System.Data.DataTable(sheetName);
            if (hdr == 1)
            {
                for (int i = 1; i <= columnsint; i++)
                {
                    dt.Columns.Add("Cloumn" + i.ToString(), System.Type.GetType("System.String"));
                }
            }
            else
            {
                for (int i = 1; i <= columnsint; i++)
                {
                    dt.Columns.Add(((Range)excelWS.Cells[1, i]).Text.ToString(), System.Type.GetType("System.String"));
                }
            }

            for (int i = hdr; i <= rowsint; i++)//行
            {
                DataRow dr = dt.NewRow();
                for (int j = 1; j <= columnsint; j++)//列
                {
                    dr[j - 1] = ((Range)excelWS.Cells[i, j]).Text.ToString();
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        public string GetColumnStringByIndex(int columnIndex)
        {
            string str;
            str = ((Range)excelWS.Cells[rowsint - 1, columnIndex]).Text.ToString();
            for (int i = 2; i <= rowsint; i++)
            {
                if (!(((Range)excelWS.Cells[i, columnIndex]).Text.ToString() as string).Equals(str))
                {
                    throw new Exception(fileName + "文件内" + sheetName + "内第" + columnIndex + "列下各行值不同，无法获取");
                }
            }
            return str;
        }

        public string GetCellStringByIndex(int rowIndex, int columnIndex)
        {
            return ((Range)excelWS.Cells[rowIndex, columnIndex]).Text.ToString();
        }

        public string[] GetColumnByIndex(int columnIndex)
        {
            string[] str = new string[rowsint - hdr + 1];
            for (int i = hdr; i <= rowsint; i++)
            {
                str[i - hdr] = ((Range)excelWS.Cells[i, columnIndex]).Text.ToString();
            }
            return str;
        }

        // 释放资源  
        private void ReleaseCOM(object pObj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj);
            }
            catch
            {
                throw new Exception("释放资源时发生错误！");
            }
            finally
            {
                pObj = null;
            }
        }
    }
}
